package com.tabnet.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import com.tabnet.beans.produit.Accessoire;
import com.tabnet.beans.produit.Produit;
import com.tabnet.dao.DAOAccessoire;
import com.tabnet.dao.DAOAccessoireCMD;
import com.tabnet.dao.DAOException;
import com.tabnet.dao.DAOFactory;
import com.tabnet.dao.DAOUtilitaire;

public class AccessoireDAOImpl implements DAOAccessoire {
	
	private static final String SQL_LISTER  		= "SELECT Ref_ACC, Description_ACC, PUA FROM Accessoire a, ConfigurerACC c "
													+ "WHERE a.Ref_ACC = c.ACC_Ref AND PRD_Ref = ?";
	private static final String SQL_LISTER_TOUT  	= "SELECT Ref_ACC, Description_ACC, PUA FROM Accessoire";
	private static final String SQL_LIER  			= "INSERT INTO ConfigurerACC VALUES (?, ?)";
	
	private static final String SQL_INSERT			= "INSERT INTO Accessoire VALUES (?, ?, ?)";
	private static final String SQL_UPDATE			= "UPDATE Accessoire SET Description_ACC=?, PUA=? WHERE Ref_ACC=?";
	private static final String SQL_DELETE			= "DELETE FROM Accessoire WHERE Ref_ACC=?";
	
    private DAOFactory daoFactory;
    private DAOAccessoireCMD _DaoAccessoireCMD;
	
    public AccessoireDAOImpl( DAOFactory daoFactory ) {
        this.daoFactory = daoFactory;
        this._DaoAccessoireCMD = this.daoFactory.getAccessoireCMDDAO();
    }
    

	@Override
	public Map<String, Accessoire> listerAccessoire(String refProd) throws DAOException {
		Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Map<String, Accessoire> lesAccessoires = new HashMap<String, Accessoire>();
        Accessoire accessoire;

        try {
            connection = daoFactory.getConnection();
            // récupération des fournissseurs
            preparedStatement = DAOUtilitaire.initialisationRequetePreparee( connection, SQL_LISTER, false,
            		refProd);
            resultSet = preparedStatement.executeQuery();
            while ( resultSet.next() ) {
            	accessoire = map( resultSet );
            	lesAccessoires.put(accessoire.getRef(), accessoire);
            }
        } catch ( SQLException e ) {
            throw new DAOException( e );
        } finally {
            DAOUtilitaire.fermeturesSilencieuses( resultSet, preparedStatement, connection );
        }

        return lesAccessoires;
    }

    private static Accessoire map( ResultSet resultSet ) throws SQLException {
    	Accessoire accessoire = new Accessoire();
    	
    	accessoire.setRef( resultSet.getString( "Ref_ACC" ) );
    	accessoire.setDescription( resultSet.getString( "Description_ACC" ) );
    	accessoire.set_puA(resultSet.getDouble( "PUA" ));
    
        return accessoire;
    }

	@Override
	public Map<String, Accessoire> listerAccessoire() throws DAOException {
		Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Map<String, Accessoire> lesAccessoires = new HashMap<String, Accessoire>();
        Accessoire accessoire;

        try {
            connection = daoFactory.getConnection();
            // récupération des fournissseurs
            preparedStatement = DAOUtilitaire.initialisationRequetePreparee( connection, SQL_LISTER_TOUT, false);
            resultSet = preparedStatement.executeQuery();
            while ( resultSet.next() ) {
            	accessoire = map( resultSet );
            	lesAccessoires.put(accessoire.getRef(), accessoire);
            }
        } catch ( SQLException e ) {
            throw new DAOException( e );
        } finally {
            DAOUtilitaire.fermeturesSilencieuses( resultSet, preparedStatement, connection );
        }

        return lesAccessoires;
	}

	@Override
	public void lierAccessoires(Produit prod) throws DAOException {
		// lie les accessoires du produit dans la base de données
		Connection connexion = null;
        ResultSet valeursAutoGenerees = null;
        
        try {
            connexion = daoFactory.getConnection();
            // pour chaque ligne des accessoires :
    		for(Accessoire acc : prod.getLstAccessoireP().values()){
    			//insertion
    			PreparedStatement preparedStatement = DAOUtilitaire.initialisationRequetePreparee( connexion, SQL_LIER, true,
    	        		 prod.getRef(), acc.getRef());
                preparedStatement.executeUpdate();
                preparedStatement.close();
    		}
        } catch ( SQLException e ) {
            throw new DAOException( e );
        } finally {
        	PreparedStatement preparedStatementF = null;
        	DAOUtilitaire.fermeturesSilencieuses( valeursAutoGenerees, preparedStatementF, connexion );
        }
	}


	@Override
	public void addAccessoire(Accessoire pAccessoire) throws DAOException {
		Connection oConnexion = null;
        PreparedStatement oPreparedStatement = null;
        ResultSet valeursAutoGenerees = null;
        
        try {
        	oConnexion = this.daoFactory.getConnection();
                    	
            //insertion d'un accessoire
            oPreparedStatement = DAOUtilitaire.initialisationRequetePreparee( oConnexion, SQL_INSERT, true,
            		pAccessoire.getRef(), pAccessoire.getDescription(), pAccessoire.get_puA());
            
            if ( oPreparedStatement.executeUpdate() == 0 )
                throw new DAOException( "Échec de la création d'un accessoire." );
            
            //insertion de cette modification dans la table des accessoires commander
            this._DaoAccessoireCMD.addAccessoire(pAccessoire);
        }
        catch ( SQLException e ) {
            throw new DAOException( e );
        }
        finally {
        	 DAOUtilitaire.fermeturesSilencieuses( valeursAutoGenerees, oPreparedStatement, oConnexion );
        }
	}


	@Override
	public void updAccessoire(Accessoire pAccessoire) throws DAOException {
		Connection oConnexion = null;
        PreparedStatement oPreparedStatement = null;
        ResultSet valeursAutoGenerees = null;
        
        try {
        	oConnexion = this.daoFactory.getConnection();
                    	
            //mise a jour d'un accessoire
            oPreparedStatement = DAOUtilitaire.initialisationRequetePreparee( oConnexion, SQL_UPDATE, true,
            		pAccessoire.getDescription(), pAccessoire.get_puA(), pAccessoire.getRef());
            
            if ( oPreparedStatement.executeUpdate() == 0 )
                throw new DAOException( "Échec de la mise a jour d'un accessoire." );
            
            //insertion de cette modification dans la table des accessoires commander
            this._DaoAccessoireCMD.addAccessoire(pAccessoire);
        }
        catch ( SQLException e ) {
            throw new DAOException( e );
        }
        finally {
        	 DAOUtilitaire.fermeturesSilencieuses( valeursAutoGenerees, oPreparedStatement, oConnexion );
        }
	}


	@Override
	public void delAccessoire(String pRef) throws DAOException {
		Connection oConnexion = null;
        PreparedStatement oPreparedStatement = null;
        ResultSet valeursAutoGenerees = null;
        
        try {
        	oConnexion = this.daoFactory.getConnection();
                    	
            //suppression d'un accessoire
            oPreparedStatement = DAOUtilitaire.initialisationRequetePreparee( oConnexion, SQL_DELETE, true, pRef);
            
            if ( oPreparedStatement.executeUpdate() == 0 )
                throw new DAOException( "Échec de la suppression d'un accessoire." );
        }
        catch ( SQLException e ) {
            throw new DAOException( e );
        }
        finally {
        	 DAOUtilitaire.fermeturesSilencieuses( valeursAutoGenerees, oPreparedStatement, oConnexion );
        }
	}
	
}
